/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package XML;

import db.definitions.ColumnDefinition;
import java.util.ArrayList;
import java.util.Iterator;
import org.dom4j.DocumentException;

/**
 *
 * @author abracadabuda
 */
public class XmlModul {

    private XmlConfig xmlConfig;
    private String feedback;
    private XmlLoader xmlLoader;

    /**
     * loads config and load scripts for creating feedback database
     * @throws org.dom4j.DocumentException if problem with XML occured
     * @throws java.lang.Exception
     */
    public XmlModul() throws DocumentException, Exception {
        loadConfig();
        feedback = loadDB("scripts");
    }

    /**
     * loads xmlConfig
     * @return xmlConfig
     */
    private XmlConfig loadConfig() {
        xmlConfig = new XmlConfig();
        xmlConfig.loadConfig();
        return xmlConfig;
    }

    /**
     * loads datbase definition from XML files stored in "scripts/" directory
     * @return SQL code for database
     * @throws org.dom4j.DocumentException if problem with XML occurs
     * @throws java.lang.Exception
     */
    private String loadDB(String directoryName) throws DocumentException, Exception {
        xmlLoader = new XmlLoader();
        return xmlLoader.loadXml(directoryName, xmlConfig.getSchema_name());
    }

    private String createTable(String type) {
        String table_name;
        String table;
        ArrayList<ColumnDefinition> pks;

        if (type.equals("objects")) { //create table objects
            table = xmlConfig.getTable_objects();
            pks = xmlConfig.getTable_objects_pks();
            table_name = "objects";
        } else {
            table = xmlConfig.getTable_users(); //create table users
            if (table.equals("")) { //if table users doesn't exist in existing database create a new simple one and exit
                String sql = "CREATE TABLE \"" + xmlConfig.getSchema_name() + "\".\"users\" \n";
                sql += "(id SERIAL, " +
                        "login varchar, " +
                        "password varchar(32), " +
                        "PRIMARY KEY(id))";
                return sql + ";";
            }
            pks = xmlConfig.getTable_users_pks();
            table_name = "users";
        }
        String sql = "CREATE TABLE "; //SQL code for joining table
        sql += "\"" + xmlConfig.getSchema_name() + "\".";
        sql += "\"" + table_name + "\" (\n";
        sql += "\"id\" serial, \n";
        String columnsRef = "";
        String columnsFK = "";
        for (Iterator i = pks.iterator(); i.hasNext();) {
            ColumnDefinition column = (ColumnDefinition) i.next();
            sql += "\"" + column.getColName() + "_" + table + "\" " + column.getType() + " NOT NULL";
            columnsRef += "\"" + column.getColName() + "_" + table + "\"";
            columnsFK += column.getColName();
            if (i.hasNext()) {
                sql += ",\n";
                columnsRef += ", ";
                columnsFK += ", ";
            }
        }
        sql += ",\nPRIMARY KEY(id),";
        sql += "\nFOREIGN KEY (" + columnsRef + ") REFERENCES \"" + xmlConfig.getTables_schema() + "\".\"" + table + "\" (" + columnsFK + ") DEFERRABLE INITIALLY DEFERRED";
        sql += "\n)";
        return sql + ";\n";
    }

    private String getTrigger(String type) { //creates trigger
        String newTable;
        String oldTable;
        ArrayList<ColumnDefinition> pks;
        String newSchema = xmlConfig.getSchema_name();
        String oldSchema = xmlConfig.getTables_schema();

        if (type.equals("objects")) {
            oldTable = xmlConfig.getTable_objects();
            pks = xmlConfig.getTable_objects_pks();
            newTable = "objects";
        } else {
            oldTable = xmlConfig.getTable_users();
            pks = xmlConfig.getTable_users_pks();
            newTable = "users";
            if (oldTable.equals("")) { //if users are not defined, no trigger have to be created
                return "";
            }
        }

        String where = "";
        for (Iterator i = pks.iterator(); i.hasNext();) {
            ColumnDefinition column = (ColumnDefinition) i.next();
            where += "\"" + column.getColName() + "_" + oldTable + "\"" + " = OLD." + column.getColName();
            if (i.hasNext()) {
                where += " AND ";
            }
        }

        String sql = "CREATE OR REPLACE FUNCTION \"" + xmlConfig.getTables_schema() + "\".\"" + xmlConfig.getSchema_name() + "_update_" + oldTable + "\" ()\n";
        sql += "RETURNS trigger AS \n";
        sql += "$BODY$\n";
        sql += "BEGIN\n";
        sql += "IF (TG_OP = 'DELETE') THEN\n";
        String del = "DELETE FROM \"" + newSchema + "\".\"" + newTable + "\" WHERE ";
        sql += del + where + ";\n";
        sql += "RETURN OLD;\n";

        sql += "ELSIF (TG_OP = 'UPDATE') THEN\n";
        String up = "UPDATE \"" + newSchema + "\".\"" + newTable + "\" SET ";
        String upTmp = "";
        for (Iterator i = pks.iterator(); i.hasNext();) {
            ColumnDefinition column = (ColumnDefinition) i.next();
            upTmp += "\"" + column.getColName() + "_" + oldTable + "\"" + " = NEW." + column.getColName();
            if (i.hasNext()) {
                upTmp += ", ";
            }
        }
        up += upTmp + " WHERE " + where + ";\n";
        sql += up + "RETURN NEW;\n";

        sql += "ELSIF (TG_OP = 'INSERT') THEN\n";
        String ins = "INSERT INTO \"" + newSchema + "\".\"" + newTable + "\"";
        String insColumns = "";
        String insValues = "";
        for (Iterator i = pks.iterator(); i.hasNext();) {
            ColumnDefinition column = (ColumnDefinition) i.next();
            insColumns += "\"" + column.getColName() + "_" + oldTable + "\"";
            insValues += "NEW." + column.getColName();
            if (i.hasNext()) {
                insColumns += ", ";
                insValues += ", ";
            }
        }
        sql += ins + "(" + insColumns + ") VALUES (" + insValues + ");\n";
        sql += "RETURN NEW;\n";
        sql += "END IF;\n";
        sql += "END;\n";
        sql += "$BODY$\n" +
                "LANGUAGE PLpgSQL \n" +
                "CALLED ON NULL INPUT \n" +
                "VOLATILE \n" +
                "EXTERNAL SECURITY INVOKER;\n";
        sql += "CREATE TRIGGER \"" + xmlConfig.getSchema_name() + "_update_" + oldTable + "_trigger\" ";
        sql += "AFTER INSERT OR UPDATE OR DELETE\n";
        sql += "ON \"" + oldSchema + "\".\"" + oldTable + "\" FOR EACH ROW\n";
        sql += "EXECUTE PROCEDURE \"" + xmlConfig.getTables_schema() + "\".\"" + xmlConfig.getSchema_name() + "_update_" + oldTable + "\"();\n";
        return sql;
    }

    /**
     * fills the created table with data of existing table
     * @param table table to be filled (objects or users)
     * @return SQL code
     */
    public String getDump(String table) {
        String newTable;
        String oldTable;
        ArrayList<ColumnDefinition> pks;
        String newSchema = xmlConfig.getSchema_name();
        String oldSchema = xmlConfig.getTables_schema();

        if (table.equals("objects")) {
            oldTable = xmlConfig.getTable_objects();
            pks = xmlConfig.getTable_objects_pks();
            newTable = "objects";
        } else {
            oldTable = xmlConfig.getTable_users();
            pks = xmlConfig.getTable_users_pks();
            newTable = "users";
            if (oldTable.equals("")) { //if where was no existing users table, fill just with 1 user
                return "INSERT INTO \"" + newSchema + "\".\"users\"(id, login, password) VALUES (1, '" + xmlConfig.getUsername() + "', md5('" + xmlConfig.getPassword() + "')); --DEFAULT USER\n";
            }
        }

        String sql = "INSERT INTO \"" + newSchema + "\".\"" + newTable + "\"";
        String insColumnsTo = "";
        String insColumnsFrom = "";
        for (Iterator i = pks.iterator(); i.hasNext();) {
            ColumnDefinition column = (ColumnDefinition) i.next();
            insColumnsTo += "\"" + column.getColName() + "_" + oldTable + "\"";
            insColumnsFrom += column.getColName();
            if (i.hasNext()) {
                insColumnsTo += ", ";
                insColumnsFrom += ", ";
            }
        }

        sql += " (" + insColumnsTo + ") (SELECT " + insColumnsFrom + " FROM \"" + oldSchema + "\".\"" + oldTable + "\")";
        return sql + ";\n";
    }

    /**
     * returns SQL code for creating schema and tables for objects and users and references to original database
     * @return
     */
    private String createJoinTables() {
        String sql = "-- creating new schema\n";
        sql += "CREATE SCHEMA " + xmlConfig.getSchema_name() + ";\n";

        sql += "-- creating tables for objects and users in feedback schema\n";
        sql += createTable("objects") + "\n";
        sql += createTable("users") + "\n";

        sql += "-- creating triggers for updating objects and users\n";
        sql += getTrigger("objects") + "\n";
        sql += getTrigger("users") + "\n";

        sql += "-- filling tables with existing data\n";
        sql += getDump("objects") + "\n";
        sql += getDump("users") + "\n";

        return sql;
    }

    /**
     * returns SQL code for tables to join existing database with generated database, creates tables, triggers and selects to fill generated tables
     * @return SQL code of generated tables and triggers
     */
    @SuppressWarnings("empty-statement")
    public String getSQL() {
        String sql = "\n\n\n\n-- DROP SCHEMA \"" + xmlConfig.getSchema_name() + "\" CASCADE;\n";
        sql += "-- DROP FUNCTION \"" + xmlConfig.getTables_schema() + "\".\"dotazniky_update_objects()\" CASCADE;";
        sql += xmlConfig.getTable_users().equals("") ? "" : "\n-- DROP FUNCTION \"" + xmlConfig.getTables_schema() + "\".\"dotazniky_update_users()\" CASCADE;";
        return createJoinTables() + feedback + sql;
    }

    public String getSql(int type) {
        if (type == 0) {
            return createJoinTables();
        } else {
            if (xmlLoader != null) {
                return xmlLoader.getSql(type);
            } else {
                return "";
            }
        }
    }
}
